﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class BusinessCardService : BaseService
    {
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="description"></param>
        /// <param name="descriptionEn"></param>
        /// <param name="sort"></param>
        /// <param name="filedata"></param>
        /// <param name="businessCardCategoryId"> </param>
        /// <returns></returns>
        public string[] Add(string name, string nameEn, string description, string descriptionEn, int sort, HttpPostedFileBase filedata = null, int businessCardCategoryId = 0)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var fileName = string.Empty;
            var thumFileName = string.Empty;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                                       {
                                           PostedFile = filedata,
                                           RelativePath = "~/Upload/BusinessCard/",
                                           IsAdaptive = true,
                                           IsImage = true,
                                           IsGenerateThumbnail = true,
                                           FileMaxLength = 1024 * 1024 * 2,
                                           ThumbnailHeight = 185,
                                           ThumbnailWidth = 165,
                                           ThumbnailPrefix = "thum"
                                       };
                uploadHelper.Save();
                fileName = uploadHelper.FileName;
                thumFileName = uploadHelper.ThumbnailFileName;
            }
            var sb = new StringBuilder();
            sb.Append("insert into tbBusinessCard(");
            sb.Append("CoNameEn,CoNormalImage,CoSmallImage,CoBigImage,CoName,CoDescription,CoDescriptionEn,CoSort,CoBusinessCardCategoryId,CoCategoryPath,CoPinYin");
            sb.Append(") values(");
            sb.Append("@CoNameEn,@CoNormalImage,@CoSmallImage,@CoBigImage,@CoName,@CoDescription,@CoDescriptionEn,@CoSort,@CoBusinessCardCategoryId,@CoCategoryPath,@CoPinYin");
            sb.Append(");select last_insert_rowid() from tbBusinessCard");
            var parameter = new[]
                {
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255),
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                    new SQLiteParameter("@CoDescription", DbType.String),
                    new SQLiteParameter("@CoDescriptionEn", DbType.String),
                    new SQLiteParameter("@CoSort", DbType.Int32),
                    new SQLiteParameter("@CoBusinessCardCategoryId", DbType.Int32),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                       new SQLiteParameter("@CoPinYin", DbType.String, 255)
                };

            parameter[0].Value = fileName;
            parameter[1].Value = thumFileName;
            parameter[2].Value = fileName;
            parameter[3].Value = name;
            parameter[4].Value = nameEn;
            parameter[5].Value = description;
            parameter[6].Value = descriptionEn;
            parameter[7].Value = sort;
            parameter[8].Value = businessCardCategoryId;
            parameter[9].Value = "";
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from tbBusinessCardCategory where CoBusinessCardCategoryId=@CoBusinessCardCategoryId", parameter))
            {
                if (sdr.Read())
                {
                    parameter[8].Value = businessCardCategoryId;
                    parameter[9].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parameter[8].Value = DBNull.Value;
                    parameter[9].Value = DBNull.Value;
                }
            }
            parameter[10].Value = ChineseUtil.ToChineseSpell(name);
            var rows = DbHelperSqLite.GetSingle(sb.ToString(), parameter).SafeToInt();
            if (rows > 0)
            {
                return new[] { rows.ToString(CultureInfo.InvariantCulture), fileName, thumFileName };
            }
            throw new Exception("添加失败！");
        }
        public int Eidt(int id, string name, string nameEn, string description, string descriptionEn, int sort, HttpPostedFileBase filedata = null, int businessCardCategoryId = 0)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var sb = new StringBuilder();
            sb.Append("update tbBusinessCard set ");
            sb.Append(" CoName=@CoName,CoDescription=@CoDescription,CoNameEn=@CoNameEn,CoDescriptionEn=@CoDescriptionEn,CoBusinessCardCategoryId=@CoBusinessCardCategoryId,CoCategoryPath=@CoCategoryPath,CoPinYin=@CoPinYin");
            if (filedata != null && filedata.ContentLength > 0)
            {
                sb.Append(",CoNormalImage=@CoNormalImage,CoSmallImage=@CoSmallImage,CoBigImage=@CoBigImage");
            }
            sb.Append(" where CoBusinessCardId=@id");
            var parameter = new[]
                {
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoDescription", DbType.String),
                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                    new SQLiteParameter("@CoDescriptionEn", DbType.String),
                    new SQLiteParameter("@CoSort", DbType.Int32),
                    new SQLiteParameter("@id", DbType.Int32),
                    new SQLiteParameter("@CoNormalImage", DbType.String, 255),
                    new SQLiteParameter("@CoSmallImage", DbType.String, 255),
                    new SQLiteParameter("@CoBigImage", DbType.String, 255),
                    new SQLiteParameter("@CoBusinessCardCategoryId", DbType.Int32),
                    new SQLiteParameter("@CoCategoryPath", DbType.String),
                    new SQLiteParameter("@CoPinYin", DbType.String, 255)
                };

            parameter[0].Value = name;
            parameter[1].Value = description;
            parameter[2].Value = nameEn;
            parameter[3].Value = descriptionEn;
            parameter[4].Value = sort;
            parameter[5].Value = id;
            if (filedata != null && filedata.ContentLength > 0)
            {

                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = "~/Upload/BusinessCard/",
                    IsAdaptive = true,
                    IsImage = true,
                    IsGenerateThumbnail = true,
                    FileMaxLength = 1024 * 1024 * 2,
                    ThumbnailHeight = 185,
                    ThumbnailWidth = 165,
                    ThumbnailPrefix = "thum"
                };
                uploadHelper.Save();
                var fileName = uploadHelper.FileName;
                var thumFileName = uploadHelper.ThumbnailFileName;
                parameter[6].Value = fileName;
                parameter[7].Value = thumFileName;
                parameter[8].Value = fileName;
                var path = HttpContext.Current.Server.MapPath("~/Upload/BusinessCard/");
                var sbtwo = new StringBuilder();
                sbtwo.Append("select * from tbBusinessCard where coBusinessCardid=@id");
                var parametertwo = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
                parametertwo[0].Value = id;
                var ds = DbHelperSqLite.Query(sbtwo.ToString(), parametertwo);
                foreach (DataRow item in ds.Tables["ds"].Rows)
                {
                    FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
                }
            }
            parameter[9].Value = businessCardCategoryId;
            parameter[10].Value = "";
            using (var sdr = DbHelperSqLite.ExecuteReader("select * from tbBusinessCardCategory where CoBusinessCardCategoryId=@CoBusinessCardCategoryId", parameter))
            {
                if (sdr.Read())
                {
                    parameter[9].Value = businessCardCategoryId;
                    parameter[10].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parameter[9].Value = DBNull.Value;
                    parameter[10].Value = DBNull.Value;
                }
            }
            parameter[11].Value = ChineseUtil.ToChineseSpell(name);
            var rows = DbHelperSqLite.ExecuteSql(sb.ToString(), parameter).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("修改失败！");
        }
        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/BusinessCard/");
            var sb = new StringBuilder();
            sb.Append("select * from tbBusinessCard where coBusinessCardid=@id");
            var parameter = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            parameter[0].Value = id;
            var ds = DbHelperSqLite.Query(sb.ToString(), parameter);
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            var sbTwo = new StringBuilder();
            sbTwo.Append("delete  from tbBusinessCard where coBusinessCardid=@id");
            var paramTwo = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            paramTwo[0].Value = id;
            int rows = DbHelperSqLite.ExecuteSql(sbTwo.ToString(), paramTwo);
            if (!(rows > 0))
                throw new Exception("记录不存在！");
        }

        public Table GetTable(string name, int pageValue, int rowsValue, int businessCardCategoryId = 0)
        {
            var strWhere = new StringBuilder();
            strWhere.Append(" where (CoName like '%'||@CoName||'%' escape '\\' or  CoNameEn like '%'||@CoName||'%' escape '\\') ");
            if (businessCardCategoryId != 0)
            {
                strWhere.Append(" and CoBusinessCardCategoryId=@CoBusinessCardCategoryId ");
            }
            var parameter = new[]
                           {
                           
                               new SQLiteParameter("@startIndex", DbType.Int32),
                               new SQLiteParameter("@endIndex", DbType.Int32),   
                               new SQLiteParameter("@CoName", DbType.String, 255),
                               new SQLiteParameter("@CoBusinessCardCategoryId", DbType.Int32)
                           };
            parameter[0].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parameter[1].Value = rowsValue;
            parameter[2].Value = name.SqliteLike();
            parameter[3].Value = businessCardCategoryId;
            var sqlOne = new StringBuilder();
            sqlOne.Append("select count(*) from TbBusinessCard ");
            sqlOne.Append(strWhere);
            int total = DbHelperSqLite.GetSingle(sqlOne.ToString(), parameter).SafeToInt();
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select * from TbBusinessCard ");
            sqlTwo.Append(strWhere);
            sqlTwo.Append(" order by cosort desc  ");
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parameter);
            return new Table { Rows = ds.Tables["ds"].Rows, Total = total };
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            parameters[0].Value = id;
            var rows = DbHelperSqLite.Query("select *  from TbBusinessCard  where CoBusinessCardId=@id", parameters).Tables["ds"].Rows;
            return rows[0];
        }

    }
}
